package matlap.database.jdbc;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import matlap.database.ConnectionManager;
import matlap.database.dao.DistributorDAO;
import matlap.shared.Distributor;
import matlap.shared.exceptions.MyException;

public class DistributorJdbcDAO implements DistributorDAO {

	Connection con = null;

	/**
	 * Turns back the distributors number FROM the distributors table.
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */

	@Override
	public int getDistributorsNumber() throws MyException{

		int nr = 0;
		String query = "SELECT COUNT(username) AS darab FROM people WHERE role=3";

		try {
		ConnectionManager.initConnection();
		con = ConnectionManager.createConnection();
		Statement st = con.createStatement();
		ResultSet rs = st.executeQuery(query);
		while (rs.next()) {
			nr = Integer.parseInt(rs.getString("darab"));
		}
		}catch (SQLException ex) {
			throw new MyException("Lekérdezés probléma. Rendelők.");
		} catch (ClassNotFoundException e) {
			throw new MyException("Adatbázis probléma.");
		}
		finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	
	
	
		return nr;
	}

	/**
	 * Turns back the distributors FROM the people table in Distributor list.
	 */

	public List<Distributor> getDistributors() {

		List<Distributor> list = new ArrayList<Distributor>();
		String firstName;
		String lastName;
		String address;
		String userName;
		String query = "SELECT last_name, first_name, username, address FROM people WHERE "
				+ "role=3 ORDER BY last_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			int i = 0;

			while (rs.next()) {

				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				address = rs.getString("address");
				userName = rs.getString("username");
				Distributor distr = new Distributor(firstName, lastName,
						userName, address);
				list.add(i, distr);
				i++;
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	/**
	 * Turns back the distributors FROM the people table in Distributor list by
	 * adding the distributors name.
	 */

	@Override
	public List<Distributor> getDistributorsByName(String distributorName) {

		String query = " ";

		List<Distributor> list = new ArrayList<Distributor>();
		String firstName;
		String lastName;
		String address;
		String userName;
		if (distributorName.length() != 0) {
			String[] n = distributorName.split(" ");
			if (n.length == 2)
				query = "SELECT last_name, first_name, username, address FROM people WHERE "
						+ "role=3 AND ((last_name like '%"
						+ n[1]
						+ "%' AND first_name like '%"
						+ n[0]
						+ "%' ) or (last_name like '%"
						+ n[0]
						+ "%' AND first_name like '%" + n[1] + "%'))";
			else
				query = "SELECT last_name, first_name, username, address FROM people WHERE "
						+ "role=3 AND last_name like '%"
						+ distributorName
						+ "%' or first_name like '%" + distributorName + "%'";

		} else {
			query = "SELECT last_name, first_name, username, address FROM people WHERE "
					+ "role=3";
		}
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				address = rs.getString("address");
				userName = rs.getString("username");
				Distributor distr = new Distributor(firstName, lastName,
						userName, address);
				list.add(i, distr);
				i++;
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	/**
	 * Turns back the distributors name FROM the people table in Distributor
	 * list.
	 */

	@Override
	public List<Distributor> selectDistributorsName() {

		List<Distributor> list = new ArrayList<Distributor>();
		String firstName;
		String lastName;
		String query = "SELECT last_name,first_name FROM people WHERE role=3 ORDER BY last_name";
		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);
			int i = 0;

			while (rs.next()) {

				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				Distributor stu = new Distributor(firstName, lastName, null,
						null);

				list.add(i, stu);
				i++;

			}

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * Deletes the distributor FROM the people table by adding the distributors
	 * username.
	 */

	public void deleteDistributors(String userName) {

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			String s = new String("delete FROM orders WHERE username='" + userName + "'");
			st.executeUpdate(s);
			s = new String("DELETE FROM people WHERE username='"
					+ userName + "'");
			st.executeUpdate(s);
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * Turns back the distributors username FROM the people table in String
	 * list.
	 */

	@Override
	public List<String> getUserNames() {

		List<String> list = new ArrayList<String>();
		String s1;
		String query = "SELECT username FROM people WHERE role=3 ORDER BY username";

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			int i = 0;

			while (rs.next()) {

				s1 = rs.getString("username");
				list.add(i, s1);
				i++;

			}

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return list;
	}

	/**
	 * Updates the people table by adding username, first name, last name and
	 * address.
	 */

	@Override
	public void updateDistributors(String userName, String firstName,
			String lastName, String address) {

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			String s = "";
			if (lastName != "") {
				s = new String("UPDATE people SET last_name='" + lastName
						+ "'WHERE username='" + userName + "'");
				st.executeUpdate(s);
			}
			if (firstName != "") {
				s = new String("UPDATE people SET first_name='" + firstName
						+ "'WHERE username='" + userName + "'");
				st.executeUpdate(s);
			}
			if (address != "") {
				s = new String("UPDATE people SET address='" + address
						+ "'WHERE username='" + userName + "'");
				st.executeUpdate(s);
			}
		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}

	/**
	 * Turns back the distributor FROM the people table by adding username in
	 * Distributor.
	 */

	@Override
	public Distributor getDistributorsByUserName(String userName) {

		String firstName;
		String lastName;
		String address;
		Distributor distr = null;
		String query = "SELECT last_name, first_name, address FROM people WHERE username='"
				+ userName + "'";

		try {
			ConnectionManager.initConnection();
			con = ConnectionManager.createConnection();
			Statement st = con.createStatement();
			ResultSet rs = st.executeQuery(query);

			while (rs.next()) {

				firstName = rs.getString("first_name");
				lastName = rs.getString("last_name");
				address = rs.getString("address");
				distr = new Distributor(firstName, lastName, userName, address);

			}

		} catch (SQLException ex) {
			System.err.println(ex.getMessage());
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				ConnectionManager.closeConnection(con);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return distr;
	}

}
